package com.zbcbms.dao.impl;

import com.alibaba.druid.util.StringUtils;
import com.zbcbms.common.DaoImplCommon;
import com.zbcbms.dao.BookBorrowDao;
import com.zbcbms.entity.dto.AddBookBorrowDto;
import com.zbcbms.entity.chart.BookBorrowChart;
import com.zbcbms.entity.vo.BookBorrowVo;
import com.zbcbms.util.JDBCUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * @author fengq
 */
public class BookBorrowDaoImpl implements BookBorrowDao {

    private Connection connection;

    @Override
    public Boolean insertOne(AddBookBorrowDto entity) {
        connection= JDBCUtil.getConnection();
        String sql="INSERT INTO book_borrow  "+
                "(id,book_number,borrow_date,status,operator_name,user_number,create_time,update_time,is_deleted,deadline)"+
                "value(?,?,?,?,?,?,?,?,?,DATE_ADD(NOW(),INTERVAL ? day))";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, UUID.randomUUID().toString().substring(0,25));
            preparedStatement.setString(2,entity.getBookNumber());
            preparedStatement.setDate(3,new Date(System.currentTimeMillis()));
            preparedStatement.setString(4,"借用中");
            preparedStatement.setString(5,entity.getOperatorName() );
            preparedStatement.setString(6,entity.getUserNumber());
            preparedStatement.setDate(7,new Date(System.currentTimeMillis()));
            preparedStatement.setDate(8,new Date(System.currentTimeMillis()));
            preparedStatement.setInt(9,0);
            preparedStatement.setString(10,entity.getBorrowDay());
            return !preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }

    }

    @Override
    public Boolean update(AddBookBorrowDto entity) {
        connection= JDBCUtil.getConnection();
        String sql="update book_borrow set "+
                "book_number=?,borrow_date=?,status=?,operator_name=?,user_number=?,update_time=?,deadline=?,return_date=? "+
                "where id=?";
        PreparedStatement preparedStatement=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,entity.getBookNumber());
            preparedStatement.setDate(2,new Date(entity.getBorrowDate().getTime()) );
            preparedStatement.setString(3,entity.getStatus());
            preparedStatement.setString(4,entity.getOperatorName());
            preparedStatement.setString(5,entity.getUserNumber());
            preparedStatement.setDate(6,new Date(System.currentTimeMillis()));
            preparedStatement.setDate(7,new Date(entity.getDeadline().getTime()));
            if (entity.getReturnDate()==null||entity.getReturnDate().toString()==""){
                preparedStatement.setDate(8,null);
            }else {
                preparedStatement.setDate(8,new Date(entity.getReturnDate().getTime()));
            }
            preparedStatement.setString(9,entity.getId());
            return !preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public Boolean deleteById(String id) {
        connection= JDBCUtil.getConnection();
        String sql="UPDATE book_borrow SET is_deleted=? " +
                "WHERE id= ? and is_deleted=0 ";
        return DaoImplCommon.deleteByCondition(connection,sql,id,1);
    }

    @Override
    public int deleteByIds(List<String> ids) {
        connection= JDBCUtil.getConnection();
        String sql="UPDATE book_borrow SET is_deleted=1 " +
                "WHERE book_id= ? and is_deleted=0 ";
        PreparedStatement preparedStatement=null;
        try {
            for (String id:ids){
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setString(1,id);
                preparedStatement.addBatch();
            }
            int[] ints = preparedStatement.executeBatch();
            preparedStatement.clearBatch();
            return ints.length;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public long selectCount(String args) {
        connection=JDBCUtil.getConnection();
        return DaoImplCommon.selectCount(connection,"book_borrow",null,null);
    }

    @Override
    public Boolean pjySicsDeleteById(String id) {
        connection=JDBCUtil.getConnection();
        String sql="delete from book_borrow where id=? and is_deleted=1";
        return DaoImplCommon.physicalDeleteByCondition(connection,sql,id);
    }

    @Override
    public Boolean recoverById(String id) {
        connection=JDBCUtil.getConnection();
        String sql="update book_borrow set is_deleted=0 where id=? and is_deleted=1 ";
        return DaoImplCommon.recoverById(connection,sql,id);
    }

    @Override
    public List<BookBorrowVo> selectBorrowByUser(String userNumber) {
        connection=JDBCUtil.getConnection();
        String sql="select t3.id,t3.book_number,t1.book_name,borrow_date,return_date,t3.status,t2.user_name,operator_name,t3.user_number,t3.deadline " +
                "from book as t1,`user` as t2,book_borrow as t3  where t3.user_number=? and t1.is_deleted=0 and t2.is_deleted=0 and t3.is_deleted=0 " +
                "and t1.book_number=t3.book_number and t2.user_number=t3.user_number";
        PreparedStatement preparedStatement =null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,userNumber);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<BookBorrowVo> list=new ArrayList<>();
            while(resultSet.next()){
                BookBorrowVo vo = new BookBorrowVo();
                vo.setId(resultSet.getString("id"));
                vo.setBookNumber(resultSet.getString("book_number"));
                vo.setBookName(resultSet.getString("book_name"));
                vo.setBorrowDate(resultSet.getDate("borrow_date"));
                vo.setReturnDate(resultSet.getDate("return_date"));
                vo.setStatus(resultSet.getString("status"));
                vo.setUserName(resultSet.getString("user_name"));
                vo.setOperatorName(resultSet.getString("operator_name"));
                vo.setUserNumber(resultSet.getString("user_number"));
                vo.setDeadline(resultSet.getDate("deadline"));
                list.add(vo);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }

    }

    @Override
    public List<BookBorrowVo> selectAllBorrow() {
        connection=JDBCUtil.getConnection();
        String sql="select t3.id,t3.book_number,t1.book_name,borrow_date,return_date,t3.status,t2.user_name,operator_name,t3.user_number,t3.deadline " +
                "from book as t1,`user` as t2,book_borrow as t3  " +
                "where t1.is_deleted=0 and t2.is_deleted=0 and t3.is_deleted=0  " +
                "and t1.book_number=t3.book_number and t2.user_number=t3.user_number";
        return DaoImplCommon.selectAllBorrow(connection,sql);
    }

    @Override
    public long selectBorrowCountByTime(String date) {
        connection= JDBCUtil.getConnection();
        String sql="select count(*) as count from book_borrow where borrow_date=? and is_deleted=0 ";
        return new DaoImplCommon().selectBorrowCountByTime(connection,sql,date);
    }

    @Override
    public long selectReturnCountByTime(String date) {
        connection= JDBCUtil.getConnection();
        String sql="select count(*) as count from book_borrow where return_date=? and is_deleted=0 ";
        return new DaoImplCommon().selectBorrowCountByTime(connection,sql,date);
    }

    @Override
    public List<BookBorrowChart> selectCountByBookName() {
        connection=JDBCUtil.getConnection();
        String sql="select Count(*) as count ,b.book_name from book_borrow as a,book as b " +
                "WHERE a.is_deleted=0 " +
                "and b.is_deleted=0 " +
                "and a.book_number=b.book_number " +
                "GROUP BY b.book_name " +
                "ORDER BY count DESC " +
                "limit 0,20 ";
        PreparedStatement preparedStatement =null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<BookBorrowChart> list=new ArrayList<>();
            while(resultSet.next()){
                BookBorrowChart vo = new BookBorrowChart();
                vo.setCount(resultSet.getLong("count"));
                vo.setBookName(resultSet.getString("book_name"));
                list.add(vo);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<BookBorrowVo> selectAllDeletedBorrow() {
        connection=JDBCUtil.getConnection();
        String sql="select t3.id,t3.book_number,t1.book_name,borrow_date,return_date,t3.status,t2.user_name,operator_name,t3.user_number,t3.deadline " +
                "from book as t1,`user` as t2,book_borrow as t3  " +
                "where t3.is_deleted=1  " +
                "and t1.book_number=t3.book_number and t2.user_number=t3.user_number";
        return DaoImplCommon.selectAllBorrow(connection,sql);
    }

    @Override
    public boolean updateDeletedByBookNumber(String bookNumber,int isDeleted) {
        connection= JDBCUtil.getConnection();
        String sql="UPDATE book_borrow SET is_deleted=? " +
                "WHERE book_Number= ? ";
        return DaoImplCommon.deleteByCondition(connection,sql,bookNumber,isDeleted);
    }

    @Override
    public boolean updateDeletedByUserNumber(String userNumber,int isDeleted) {
        connection= JDBCUtil.getConnection();
        String sql="UPDATE book_borrow SET is_deleted=? "+
                "WHERE user_Number= ? ";
        return DaoImplCommon.deleteByCondition(connection,sql,userNumber,isDeleted);
    }

    @Override
    public List<BookBorrowVo> selectBookBorrowVoByKeyword(String keyword) {
        connection=JDBCUtil.getConnection();
        String sql="select t3.id,t3.book_number,t1.book_name,borrow_date,return_date,t3.status,t2.user_name,operator_name,t3.user_number,t3.deadline \n" +
                "                from book as t1,`user` as t2,book_borrow as t3 \n" +
                "                where t3.is_deleted=0 \n" +
                "\t\t\t\t\t\t\t\tand t1.is_deleted=0\n" +
                "\t\t\t\t\t\t\t\tand t2.is_deleted=0\n" +
                "                and t1.book_number=t3.book_number \n" +
                "\t\t\t\t\t\t\t\tand t2.user_number=t3.user_number\n" +
                "\t\t\t\t\t\t\t\tand CONCAT(t1.book_name,t2.user_name,t3.id) like ? ";
        PreparedStatement preparedStatement =null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,"%"+keyword+"%");
            ResultSet resultSet = preparedStatement.executeQuery();
            List<BookBorrowVo> list=new ArrayList<>();
            while(resultSet.next()){
                BookBorrowVo vo = new BookBorrowVo();
                vo.setId(resultSet.getString("id"));
                vo.setBookNumber(resultSet.getString("book_number"));
                vo.setBookName(resultSet.getString("book_name"));
                vo.setBorrowDate(resultSet.getDate("borrow_date"));
                vo.setDeadline(resultSet.getDate("deadline"));
                vo.setReturnDate(resultSet.getDate("return_date"));
                vo.setStatus(resultSet.getString("status"));
                vo.setUserName(resultSet.getString("user_name"));
                vo.setOperatorName(resultSet.getString("operator_name"));
                vo.setUserNumber(resultSet.getString("user_number"));
                list.add(vo);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<BookBorrowVo> selectBookBorrowVoByKeywordAndUserNumber(String keyword, String userNumber) {
        connection=JDBCUtil.getConnection();
        PreparedStatement preparedStatement =null;
        String baseSql="select t3.id,t3.book_number,t1.book_name,borrow_date,return_date,t3.status,t2.user_name,operator_name,t3.user_number,t3.deadline \n" +
                " from book as t1,`user` as t2,book_borrow as t3 \n" +
                "where t3.is_deleted=0 \n" +
                "and t1.is_deleted=0\n" +
                "and t2.is_deleted=0\n" +
                "and t1.book_number=t3.book_number \n" +
                "and t2.user_number=t3.user_number\n" +
                "and t3.user_number=? \n" ;
        try {
            if (keyword==null||keyword==""){
                preparedStatement=connection.prepareStatement(baseSql);
                preparedStatement.setString(1,userNumber);
            }else {
                String sql=baseSql+ "and CONCAT(t1.book_name,t2.user_name,t3.id,t3.status) like ? ";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setString(1,userNumber);
                preparedStatement.setString(2,"%"+keyword+"%");
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            List<BookBorrowVo> list=new ArrayList<>();
            while(resultSet.next()){
                BookBorrowVo vo = new BookBorrowVo();
                vo.setId(resultSet.getString("id"));
                vo.setBookNumber(resultSet.getString("book_number"));
                vo.setBookName(resultSet.getString("book_name"));
                vo.setBorrowDate(resultSet.getDate("borrow_date"));
                vo.setDeadline(resultSet.getDate("deadline"));
                vo.setReturnDate(resultSet.getDate("return_date"));
                vo.setStatus(resultSet.getString("status"));
                vo.setUserName(resultSet.getString("user_name"));
                vo.setOperatorName(resultSet.getString("operator_name"));
                vo.setUserNumber(resultSet.getString("user_number"));
                list.add(vo);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }

    @Override
    public List<BookBorrowVo> selectBookBorrowByUserAndDayNums(String number, String days) {
        connection=JDBCUtil.getConnection();
        String baseSql ="select t3.id,t3.book_number,t1.book_name,borrow_date,return_date,t3.status,t2.user_name,operator_name,t3.user_number,t3.deadline  \n" +
                "                 from book as t1,`user` as t2,book_borrow as t3 \n" +
                "                 where t3.is_deleted=0 " +
                "                 and t1.is_deleted=0 " +
                "                 and t2.is_deleted=0 " +
                "                 and t1.book_number=t3.book_number  " +
                "                 and t2.user_number=t3.user_number " ;
        PreparedStatement preparedStatement = null;
        try {
            ResultSet resultSet=null;
            if(number==null|| StringUtils.isEmpty(number)){
                if (days.equals("全部")){
                    String sql=baseSql;
                    preparedStatement = connection.prepareStatement(sql);
                    resultSet = preparedStatement.executeQuery();
                }else if (days.equals("已过期")){
                    String sql=baseSql+
                            " and DATE_FORMAT(t3.deadline,\"%Y-%m-%d\") < DATE_FORMAT( NOW(),\"%Y-%m-%d\") ";
                    preparedStatement = connection.prepareStatement(sql);
                    resultSet = preparedStatement.executeQuery();
                }else {
                    String sql=baseSql+ " and DATE_FORMAT(t3.deadline,\"%Y-%m-%d\") >= DATE_FORMAT( NOW(),\"%Y-%m-%d\") "+
                            "  and DATE_FORMAT(t3.deadline,\"%Y-%m-%d\") <= DATE_FORMAT( DATE_ADD(NOW(),INTERVAL ? DAY),\"%Y-%m-%d\") ";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1,days);
                    resultSet = preparedStatement.executeQuery();
                }
            }else {
                if (days.equals("全部")){
                    String sql=baseSql+ " and t3.user_number= ?";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1,number);
                    resultSet = preparedStatement.executeQuery();
                }else if (days.equals("已过期")){
                    String sql=baseSql+   " and t3.user_number= ?"+ " and DATE_FORMAT(t3.deadline,\"%Y-%m-%d\") < DATE_FORMAT( NOW(),\"%Y-%m-%d\") ";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1,number);
                    resultSet = preparedStatement.executeQuery();
                }else {
                    String sql=baseSql+ " and t3.user_number= ?"+
                            " and DATE_FORMAT(t3.deadline,\"%Y-%m-%d\") >= DATE_FORMAT( NOW(),\"%Y-%m-%d\") "+
                            "  and DATE_FORMAT(t3.deadline,\"%Y-%m-%d\") <= DATE_FORMAT( DATE_ADD(NOW(),INTERVAL ? DAY),\"%Y-%m-%d\") ";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1,number);
                    preparedStatement.setString(2,days);
                    resultSet = preparedStatement.executeQuery();
                }
            }

            List<BookBorrowVo> list=new ArrayList<>();
            while(resultSet.next()){
                BookBorrowVo vo = new BookBorrowVo();
                vo.setId(resultSet.getString("id"));
                vo.setBookNumber(resultSet.getString("book_number"));
                vo.setBookName(resultSet.getString("book_name"));
                vo.setBorrowDate(resultSet.getDate("borrow_date"));
                vo.setDeadline(resultSet.getDate("deadline"));
                vo.setReturnDate(resultSet.getDate("return_date"));
                vo.setStatus(resultSet.getString("status"));
                vo.setUserName(resultSet.getString("user_name"));
                vo.setOperatorName(resultSet.getString("operator_name"));
                vo.setUserNumber(resultSet.getString("user_number"));
                list.add(vo);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            JDBCUtil.closeResource(connection,preparedStatement);
        }
    }


}
